Share via

SQL Select subquery using exists

Joseph Mancini 81 Reputation points
2024-05-13T17:35:44.68+00:00

I am trying to write a query with a subquery. The subquery returns what is in the attached image below.

select top(1) with ties matter_uno, PART_CAT_CODE, EFF_DATE, EMPL_UNO from TBM_CLMAT_PART where MATTER_UNO = 11275 and PART_CAT_CODE = 'BILL' order by eff_date desc

Note: Most of the time, only 1 row is returned by this subquery, but sometimes, more than 1 row can be returned.

All good so far. Now, I want to take this query and add it a main query. It will sort of look like a 1 to 1 or 1 to possibly many query, again, if more than 1 row is returned as below. I can't use the In operator as I need my subquery to return more than 1 field, so I am trying the Exists operator. But when I use the Exists operator in my query, only the record with empl_uno of 2979 is returned.

Capture

This is the complete query I am using below. Hbm_Matter will only 1 have unique record(matter_uno). How do I get multiple records returned in my query when there are 2 records in the subquery?

select matter_uno, matter_name, bill_empl_uno from hbm_matter a where exists ( select top(1) with ties matter_uno, PART_CAT_CODE, EFF_DATE, EMPL_UNO from TBM_CLMAT_PART where matter_uno = a.MATTER_UNO and PART_CAT_CODE = 'BILL' order by eff_date desc) and status_code = 'O' and inactive = 'N' and MATTER_UNO = 11275 order by a.MATTER_UNO

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,914 questions
0 comments
Accepted answer
  1. Erland Sommarskog 102.4K Reputation points
    2024-05-13T21:14:32.6166667+00:00

    This is a bit of a guessing game, but it sounds like this what you are looking for:

    select matter_uno, matter_name, bill_empl_uno 
from  hbm_matter a 
cross apply  ( select top(1) with ties matter_uno, PART_CAT_CODE, EFF_DATE, EMPL_UNO 
               from TBM_CLMAT_PART 
               where matter_uno = a.MATTER_UNO 
                 and PART_CAT_CODE = 'BILL' 
               order by eff_date desc) as b
where status_code = 'O' 
  and inactive = 'N' 
  and MATTER_UNO = 11275 
order by a.MATTER_UNO

    If this is off the mark, I think it helps if you post CREATE TABLE statements for your tables, INSERT statements with sample data and the desired result given the sample, so we can develop a tested solution.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bruce (SqlWork.com) 58,121 Reputation points
    2024-05-13T17:44:36.7233333+00:00

    a subquery only returns one column (only true/false if used in "exists"). if not used in an "in" clause, returning more than 1 row is an error. if you need multiple columns or rows, then use a join.

  2. CosmogHong-MSFT 23,946 Reputation points Microsoft Vendor
    2024-05-14T02:26:21.54+00:00

    Hi @Joseph Mancini

    I tried a few other things with no luck, but I am missing something and am not sure what.

    Your left join query seems no syntax error.

    Have you check if remove where m.BILL_EMPL_UNO = 2979 will work? I suspect it is the where condition that filters out the result set of the subquery。

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments